﻿/*
无限级分类(非递归算法/存储过程版/GUID主键)完整数据库示例_(1)表结构

无限分类是一个老生常谈的话题了，网上有很多解决方案，可以分成二个流派，一种利用递归，一种利用非递归(当然需要其它一些辅助手段判断节点层次)，但核心表结构都差不多，有三个关键字段(ID主键,ParentId上级类 id,ClassName类名--理论上讲，如果用递归，这三个字段就足够了)，完整表结构如下


*/
Create TABLE [dbo].[T_Class](
	 [F_AutoId] [int] IDENTITY(1,1) NOT NULL,
	 [F_ClsID] Int Default(0) NOT NULL,
	 [F_ClassName] varchar(256) DEFAULT ('') NOT NULL,
	 [F_ShortName] varchar(50) DEFAULT ('') NOT NULL,
	 [F_ParentID]  varchar(256) DEFAULT ('') NOT NULL,
	 [F_Depth] Int Default(0) NOT NULL,
	 [F_RootID] Int Default(0) NOT NULL,
	 [F_Orders] Int Default(0) NOT NULL,
	 [F_ParentIDStr] varchar(300) DEFAULT ('') NOT NULL,--可以属于多个 大类
	 [F_ParentNameStr] varchar(300) DEFAULT ('') NOT NULL,
	 [F_ReadMe] varchar(250) DEFAULT ('') NOT NULL,
	 [F_AddTime] datetime DEFAULT (getdate()) NOT NULL,
	 [F_Type] varchar(50) DEFAULT ('') NOT NULL,
	 [F_MaxPage]  Int Default(0) NOT NULL,
	 [F_No] varchar(50) DEFAULT ('') NOT NULL,
	 [F_IsShow] tinyint Default(0) NOT NULL,
 CONSTRAINT [PK_T_Class] PRIMARY KEY CLUSTERED
(
 [F_ClsID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]



/*************************/
/*

无限级分类(非递归算法/存储过程版/GUID主键)完整数据库示例_(2)插入记录
-- ========================================
-- Author:  <杨俊明,jimmy.yang@cntvs.com>
-- Description: <无限级分类插入记录>
-- Return     : 成功返回0,重名返回1
-- ========================================

Create PROCEDURE [dbo].[up_Class_InsertEx]
 @newId uniqueidentifier, --新记录Id 
 @classType nvarchar(50), --类型(比如:产品,新闻,地区)
 @parentId uniqueidentifier, --父类Id
 @className nvarchar(50), --分类名称
 @classReadMe nvarchar(200) --分类说明 
 
AS
BEGIN 
 SET NOCOUNT ON; 
 Declare @RootID int;     --根ID(顶级分类的RootID)
 DeClare @ParentName nvarchar(500);  --父类名称
 Declare @Depth int;      --父类深度
 Declare @MaxOrders int;     --同级最大排序号
 Declare @ParentIdStr nvarchar(500);  --父类Id全路径
 Declare @ParentNameStr nvarchar(500); --父类名称全路径 
 
 --如果是顶级类
 if @parentId='00000000-0000-0000-0000-000000000000'
 begin
  if not exists(select F_id From T_Class where F_ClassName=@className and F_type=@ClassType and F_parentid='00000000-0000-0000-0000-000000000000')  
  begin
   set @RootID = 0   
   if exists(select F_id From T_Class Where F_type=@ClassType and F_parentid='00000000-0000-0000-0000-000000000000')
   begin
    select @RootId = max(F_RootID) From T_Class Where F_type=@ClassType and F_parentid='00000000-0000-0000-0000-000000000000';--得到当前顶级分类的最大RootId    
   end 
        
   insert into T_Class(
    F_Id,
    F_Type,
    F_parentId,
    F_className,
    F_ReadMe,
    F_parentIdstr,
    F_parentNameStr,
    F_RootId,
    F_orders)
   values(
    @newId,
    @classType,
    '00000000-0000-0000-0000-000000000000',
    @className,
    @classReadMe,
    '00000000-0000-0000-0000-000000000000',
    '00000000-0000-0000-0000-000000000000',
    @rootId+1,
    0)
   return 0;--顶级分类成功插入
  end
  else
   return 1;--顶级分类已经存在
 end

 --如果不是顶级类
 if @parentId<>'00000000-0000-0000-0000-000000000000'   
 begin
  --检查父类ID的合法性
  if exists(select F_id From T_Class Where F_id=@parentID)  
  begin
   --检查该节点是否已经存在  
   if not exists(select F_id From T_Class where F_ClassName=@className and F_type=@ClassType and F_parentid=@parentId)   
   begin      
    select @rootId=F_RootId,@ParentName=F_ClassName,@Depth=F_Depth,@parentIdStr=F_parentIdStr,@parentNameStr=F_parentNameStr From T_Class Where F_Id = @ParentId;    
    set @maxOrders = 0
    --如果父类无子类
    if not exists(select F_id From T_Class where F_ParentIdStr like '%' + Convert(Nvarchar(50),@parentId) + '%')     
     select @maxOrders = F_orders From T_Class where F_id=@parentId    
    else
     select @maxOrders = Max(F_orders) From T_Class where F_ParentIdStr like '%' + Convert(Nvarchar(50),@parentId) + '%'           
           

    insert into T_Class(
     F_ID,
     F_Type,
     F_parentId,
     F_className,
     F_ReadMe,
     F_parentIdstr,
     F_parentNameStr,
     F_Orders,
     F_Depth,
     F_RootId  
     )
    values(
     @newId,
     @classType,
     @parentId,
     @className,
     @classReadMe,
     @parentIdStr + ',' + convert(nvarchar(50),@parentID),
     @parentNameStr + ',' + @parentName,
     @maxOrders + 1,
     @depth + 1,
     @rootId
     )
 
    update T_class set F_orders = F_orders + 1 where (F_orders > @maxOrders) And (F_RootId=@RootId) And F_ID<>@NewId 
 
    
    return 0;  
   end
   else
    return 1;
  end
 end 
END




/*
无限级分类(非递归算法/存储过程版/GUID主键)完整数据库示例_(3)删除记录

-- =============================
-- Author:  <杨俊明,Jimmy.yang@cntvs.com or yjmyzz@126.com>
-- Description: <无限级分类_删除分类>
-- Return     : 0删除正常,-1父类下还有子类
-- ===========================
*/

Alter PROCEDURE [dbo].[up_Class_DeleteEx]
 @clsId uniqueidentifier, --要删除的分类
 @withChild bit     --是否级联删除子类
AS
BEGIN
 SET NOCOUNT ON;
 Declare @so_MaxOrders int;
 Declare @s_Childs int;
 Declare @clsType Nvarchar(50);
 Declare @O_RootId int;
 
 Select @O_RootId = F_RootId,@clsType = F_Type From T_Class where F_ID=@clsId;

 If (@WithChild=1)  
  begin
   Select @so_maxOrders=max(F_orders) From T_class Where F_ID=@clsId or F_ParentIdStr like '%' + Convert(varchar(50),@clsId) + '%'; --得到自身系的最大排序号
   Select @s_Childs= Count(F_ID) from t_Class Where F_ID=@clsId or F_ParentIdStr like '%' + Convert(varchar(50),@clsId) + '%';
   Delete from T_Class where ','+F_parentIdStr+',' like '%' + convert(varchar(100),@clsId) + '%'
   Delete from T_Class Where F_Id=@ClsId 
   Update T_Class Set F_Orders = F_Orders - @s_childs where F_Type=@clsType And F_RootId=@O_RootId And F_Orders > @so_maxOrders;
   return 0
  end
 else  
  --如果不级联删除，又有子节点，则返回-1 
  If exists(select F_id from T_Class where ',' + F_parentIdStr + ',' like '%' + convert(varchar(100),@clsId) + '%')  
   return -1
  else
   begin
    Select @so_maxOrders=max(F_orders) From T_class Where F_ID=@clsId or F_ParentIdStr like '%' + Convert(varchar(50),@clsId) + '%'; --得到自身系的最大排序号
    Select @s_Childs= 1;
    Delete from T_Class Where F_Id=@ClsId;
    Update T_Class Set F_Orders = F_Orders - @s_childs where F_Type=@clsType And F_RootId=@O_RootId And F_Orders > @so_maxOrders;
    return 0
   end
   
END


/*************************/

/*
显示记录，其实再简单不过了，一条sql语句即可

Select * From T_Class order By F_RootID,F_Orders


下面给出一个ASPX(C#版)的示例

Database.ConnectionString = "Data Source=127.0.0.1;Initial Catalog=cntvs;Persist Security Info=True;User ID=sa;Password=3power";

string _strSql = "Select * from T_Class order By F_rootId,F_Orders";
SqlConnection conn = Database.GetConn();

//插入几个演示用的节点
Database.ExecuteNonQuery(conn, CommandType.Text, "truncate table T_Class");
Database.ExecuteNonQuery(conn, CommandType.Text, "exec up_Class_InsertEx 'c12fe245-75ec-4b28-82e1-99e501672f41','product','00000000-0000-0000-0000-000000000000','A',''");
Database.ExecuteNonQuery(conn, CommandType.Text, "exec up_Class_InsertEx '0c630080-51b0-4b92-bd6c-99e50167d8a5','product','c12fe245-75ec-4b28-82e1-99e501672f41','A1',''");
Database.ExecuteNonQuery(conn, CommandType.Text, "exec up_Class_InsertEx '9eeda546-927e-4b2a-a4e8-99e501681676','product','0c630080-51b0-4b92-bd6c-99e50167d8a5','A11',''");
Database.ExecuteNonQuery(conn, CommandType.Text, "exec up_Class_InsertEx 'bc36649c-736a-467f-b2f1-99e501687083','product','0c630080-51b0-4b92-bd6c-99e50167d8a5','A12',''");
Database.ExecuteNonQuery(conn, CommandType.Text, "exec up_Class_InsertEx '2b975d64-27c7-4759-b475-99e50168d622','product','c12fe245-75ec-4b28-82e1-99e501672f41','A2',''");
Database.ExecuteNonQuery(conn, CommandType.Text, "exec up_Class_InsertEx 'b2703d81-60c8-4e08-8ed4-99e50169376b','product','2b975d64-27c7-4759-b475-99e50168d622','A21',''");
Database.ExecuteNonQuery(conn, CommandType.Text, "exec up_Class_InsertEx '91ca8257-317e-4a6b-ab33-99e50169540a','product','2b975d64-27c7-4759-b475-99e50168d622','A22',''");
Database.ExecuteNonQuery(conn, CommandType.Text, "exec up_Class_InsertEx '4ce0b1de-68ab-4e2b-bb85-99e5016961b7','product','2b975d64-27c7-4759-b475-99e50168d622','A23',''");
Database.ExecuteNonQuery(conn, CommandType.Text, "exec up_Class_InsertEx 'cc05c6e0-fde8-4f1c-aac6-99e50169f995','product','4ce0b1de-68ab-4e2b-bb85-99e5016961b7','A231',''");
Database.ExecuteNonQuery(conn, CommandType.Text, "exec up_Class_InsertEx '31c710bd-d2ff-4ffb-a8e9-99e50169eee4','product','4ce0b1de-68ab-4e2b-bb85-99e5016961b7','A232',''");
Database.ExecuteNonQuery(conn, CommandType.Text, "exec up_Class_InsertEx 'c11d5fc2-cb6b-488c-b97c-99e5016a4c9a','product','c12fe245-75ec-4b28-82e1-99e501672f41','A3',''");

//读出节点
SqlDataReader sdr = Database.ExecuteReader(conn, CommandType.Text, _strSql);
while (sdr.Read())
{
    string t = "";
    for (int i = 0; i < Convert.ToInt16(sdr["F_Depth"].ToString()); i++)
    {
        t += "　|- ";
    }
    Response.Write(t + sdr["F_ClassName"].ToString() + "<br/>");
}
sdr.Close();

Database.Dispose(conn);

最终页面上的显示结果如下:

A
|- A1
|- 　|- A11
|- 　|- A12
|- A2
|- 　|- A21
|- 　|- A22
|- 　|- A23
|- 　|- 　|- A231
|- 　|- 　|- A232
|- A3

*/